﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace BasicCMS.Data
{
    public class QueryBuilder
    {
        private readonly IUnitOfWork unitOfWork;
        private IList<KeyValuePair<string, object>> parameters = new List<KeyValuePair<string, object>>();
        private string sql;
        private IDbCommand command;

        public QueryBuilder(IUnitOfWork unitOfWork, string sql)
        {
            this.unitOfWork = unitOfWork;
            this.sql = sql;
        }

        public IDbCommand Command
        {
            set { command = value; }
        }

        public QueryBuilder Param(string name, object value)
        {
            parameters.Add(new KeyValuePair<string, object>(name, value));
            return this;
        }

        public IList<T> ToList<T>()
        {
            IList<T> list = new List<T>();
            DataTable dt=new DataTable();
            command.CommandText = sql;
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                sda.SelectCommand = (SqlCommand)command;
                sda.Fill(dt);
            }

            Type type = typeof(T);
            var properties=type.GetProperties();
            foreach (DataRow row in dt.Rows)
            {
                T t = (T)Activator.CreateInstance<T>();
                foreach (var prop in properties)
                {
                    prop.SetValue(t, row[prop.Name]);
                }
                list.Add(t);
            }

            return list;
        }

        public PagedList<T> ToPagedList<T>(int pageIndex, int pageSize)
        {
            var result = new PagedList<T>();
            result.CurrentPageIndex = pageIndex;
            result.PageSize = pageSize;

            
            int orderIndex = sql.LastIndexOf("ORDER", StringComparison.CurrentCultureIgnoreCase);
            int fromIndex = sql.IndexOf("From", StringComparison.CurrentCultureIgnoreCase);

            string orderSql = sql.Substring(orderIndex);
            string sqlWithoutOrder = sql.Substring(0, orderIndex);
            string fromSql = sqlWithoutOrder.Substring(fromIndex);
            string sqlWithOutSelect = sqlWithoutOrder.Substring(sql.IndexOf("SELECT", StringComparison.CurrentCultureIgnoreCase) + 6);

            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.AppendFormat("SELECT * FROM(SELECT ROW_NUMBER() OVER({0}) N,{1}) T WHERE T.N >={2} AND T.N<={3}", orderSql, sqlWithOutSelect, (pageIndex - 1) * pageIndex + 1, pageIndex * pageSize);
            sqlBuilder.AppendLine(";");
            sqlBuilder.AppendFormat("SELECT COUNT(1) {0}", fromSql);

            DataSet ds = new DataSet();
            command.CommandText = sqlBuilder.ToString();
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                sda.SelectCommand = (SqlCommand)command;
                sda.Fill(ds);
            }

            Type type = typeof(T);
            var properties = type.GetProperties();
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                T t = (T)Activator.CreateInstance<T>();
                foreach (var prop in properties)
                {
                    prop.SetValue(t, row[prop.Name]);
                }
                result.Items.Add(t);
            }

            int count = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            result.TotalItemCount = count;

            return result;
        }
    }
}
